# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
import pymysql
from .items import *
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime, Float
import string

MYSQL_CONFIG = {
    'user': 'imooc',
    'password': 'imooc',
    'host': '127.0.0.1',
    'port': 33070,
    'db': 'imooc',
    'charset': 'utf8mb4'
}

Base = declarative_base()
engine_template = string.Template("mysql+pymysql://${user}:${password}@${host}:${port}/${db}?charset=${charset}")


engine = create_engine(engine_template.substitute(MYSQL_CONFIG))


class ImoocFreeDirection(Base):
    __tablename__ = 'imooc_free_direction'
    id = Column(Integer, primary_key=True)
    direct_name = Column(String(50))
    direct_code = Column(String(50))
    direct_url = Column(String(255))
    created_at = Column(DateTime)
    updated_at = Column(DateTime)


class ImoocFreeType(Base):
    __tablename__ = 'imooc_free_type'
    id = Column(Integer, primary_key=True)
    type_id = Column(Integer, index=True)
    type_name = Column(String(50))
    direct_code = Column(String(50))
    type_url = Column(String(255))
    created_at = Column(DateTime)
    updated_at = Column(DateTime)


class ImoocFreeCourseList(Base):
    __tablename__ = 'imooc_free_course_list'
    id = Column(Integer, primary_key=True)
    course_id = Column(Integer, index=True)
    type_id = Column(Integer)
    course_name = Column(String(100))
    course_img_url = Column(String(255))
    course_card_desc = Column(String(255))
    course_label_str = Column(String(255))
    course_level = Column(Integer)
    course_student_num = Column(Integer)
    course_price = Column(Float)
    course_cost_price = Column(Float)
    course_url = Column(String(255))
    course_description = Column(String(255))
    course_chapter_info = Column(String(6000))
    teacher_id = Column(Integer)
    teacher_url = Column(String(255))
    created_at = Column(DateTime)
    updated_at = Column(DateTime)


class ImoocTeacher(Base):
    __tablename__ = 'imooc_teacher'
    id = Column(Integer, primary_key=True)
    teacher_id = Column(Integer, index=True)
    teacher_url = Column(String(255))
    teacher_header_img = Column(String(255))
    teacher_nickname = Column(String(50))
    teacher_professional = Column(String(50))
    teacher_detail = Column(String(255))
    teacher_desc = Column(String(500))
    created_at = Column(DateTime)
    updated_at = Column(DateTime)


class ImoocFreeCourseLog(Base):
    __tablename__ = 'imooc_free_course_log'
    id = Column(Integer, primary_key=True)
    course_id = Column(Integer, index=True)
    course_student_num = Column(Integer)
    created_at = Column(DateTime)


class ImoocTeacherLog(Base):
    __tablename__ = 'imooc_teacher_log'
    id = Column(Integer, primary_key=True)
    teacher_id = Column(Integer, index=True)
    teacher_detail = Column(String(255))
    created_at = Column(DateTime)


class ImoocPipeline(object):

    def __init__(self):
        Session = sessionmaker(bind=engine)
        self.session = Session()


class ImoocFreeDirectPipline(ImoocPipeline):

    def process_item(self, item, spider):
        if isinstance(item, ImoocFreeDirectItem):
            imooc_direction = self.session.query(ImoocFreeDirection).filter(
                ImoocFreeDirection.direct_code == item['direct_code']).one_or_none()
            if not imooc_direction:
                imooc_direction = ImoocFreeDirection()
                imooc_direction.created_at = item['created_at']
            imooc_direction.direct_code = item['direct_code']
            imooc_direction.direct_name = item['direct_name']
            imooc_direction.direct_url = item['direct_url']
            imooc_direction.updated_at = item['created_at']
            self.session.add(imooc_direction)
            self.session.commit()
        return item


class ImoocFreeTypePipline(ImoocPipeline):

    def process_item(self, item, spider):
        if isinstance(item, ImoocFreeTypeItem):
            imooc_type = self.session.query(ImoocFreeType).filter(
                ImoocFreeType.type_id == item['type_id']).one_or_none()
            if not imooc_type:
                imooc_type = ImoocFreeType()
                imooc_type.created_at = item['created_at']
            imooc_type.type_id = item['type_id']
            imooc_type.type_name = item['type_name']
            imooc_type.type_url = item['type_url']
            imooc_type.direct_code = item['direct_code']
            imooc_type.updated_at = item['created_at']
            self.session.add(imooc_type)
            self.session.commit()
        return item


class ImoocFreeCourseListPipline(ImoocPipeline):

    def process_item(self, item, spider):
        if isinstance(item, ImoocFreeCourseListItem):
            imooc_course = self.session.query(ImoocFreeCourseList).filter(
                ImoocFreeCourseList.course_id == item['course_id']).one_or_none()
            if not imooc_course:
                imooc_course = ImoocFreeCourseList()
                imooc_course.created_at = item['created_at']
            imooc_course.course_id = item['course_id']
            imooc_course.type_id = item['type_id']
            imooc_course.course_name = item['course_name']
            imooc_course.course_img_url = item['course_img_url']
            imooc_course.course_card_desc = item['course_card_desc']
            imooc_course.course_label_str = item['course_label_str']
            imooc_course.course_level = item['course_level']
            imooc_course.course_student_num = item['course_student_num']
            imooc_course.course_price = item['course_price']
            imooc_course.course_cost_price = item['course_cost_price']
            imooc_course.course_url = item['course_url']
            imooc_course.updated_at = item['created_at']
            self.session.add(imooc_course)

            imooc_course_log = ImoocFreeCourseLog()
            imooc_course_log.course_id = item['course_id']
            imooc_course_log.course_student_num = item['course_student_num']
            imooc_course_log.created_at = item['created_at']
            self.session.add(imooc_course_log)

            self.session.commit()
        return item


class ImoocFreeCourseInfoPipline(ImoocPipeline):

    def process_item(self, item, spider):
        if isinstance(item, ImoocFreeCourseInfoItem):
            imooc_info = self.session.query(ImoocFreeCourseList).filter(
                ImoocFreeCourseList.course_id == item['course_id']).one_or_none()
            if not imooc_info:
                imooc_info = ImoocFreeCourseList()
                imooc_info.created_at = item['created_at']
            imooc_info.course_id = item['course_id']
            imooc_info.course_description = item['course_description']
            imooc_info.course_chapter_info = item['course_chapter_info']
            imooc_info.teacher_id = item['teacher_id']
            imooc_info.teacher_url = item['teacher_url']
            imooc_info.updated_at = item['created_at']
            self.session.add(imooc_info)
            self.session.commit()
        return item


class ImoocTeacherPipline(ImoocPipeline):

    def process_item(self, item, spider):
        if isinstance(item, ImoocTeacherItem):
            imooc_teacher = self.session.query(ImoocTeacher).filter(
                ImoocTeacher.teacher_id == item['teacher_id']).one_or_none()
            if not imooc_teacher:
                imooc_teacher = ImoocTeacher()
                imooc_teacher.created_at = item['created_at']
            imooc_teacher.teacher_id = item['teacher_id']
            imooc_teacher.teacher_url = item['teacher_url']
            imooc_teacher.teacher_header_img = item['teacher_header_img']
            imooc_teacher.teacher_nickname = item['teacher_nickname']
            imooc_teacher.teacher_professional = item['teacher_professional']
            imooc_teacher.teacher_detail = item['teacher_detail']
            imooc_teacher.teacher_desc = item['teacher_desc']
            imooc_teacher.updated_at = item['created_at']
            self.session.add(imooc_teacher)

            teacher_log = ImoocTeacherLog()
            teacher_log.teacher_id = item['teacher_id']
            teacher_log.teacher_detail = item['teacher_detail']
            teacher_log.created_at = item['created_at']
            self.session.add(teacher_log)

            self.session.commit()
        return item
